#!/bin/perl



#
#
#db2 "update regattas set REGRESS_ORDER = REGRESS_ORDER + 1 where REGRESS_ORDER >= 1"
#db2 insert into REGATTAS values ('Milton', 1)


# Transform the csv files to map team names.  Needs to be done one time only
my $mapTeamNames = 1;
if ($mapTeamNames)
{
   system("nameTransform.pl regressionOHDBC3K2011.csv");
   system("nameTransform.pl regressionMilton2011.csv");
   system("nameTransform.pl regressionPickering2011.csv");
   #system("nameTransform.pl regressionPickeringHS2011.csv");
   system("nameTransform.pl regressionCenterIsland2011.csv");
   system("nameTransform.pl regressionNationals2011.csv");
   system("nameTransform.pl regressionOttawa2011.csv");
   system("nameTransform.pl regressionWelland2011.csv");
   #system("nameTransform.pl regressionLachine2011.csv");
   system("nameTransform.pl regressionMontreal2011.csv");
   system("nameTransform.pl regressionTorontoCup2011.csv");
   system("nameTransform.pl regressionWoodstock2011.csv");
   system("nameTransform.pl regressionGWNSport2011.csv");
   system("nameTransform.pl regressionGWNC2011.csv");
   system("nameTransform.pl regressionQC2011.csv");
}

# Careful with opening CSV files in excel.  It will change the format of my times to mm:ss.0 so I need
# to change it back to mm:ss.00 before saving or else I will lose the 1/100 of a seconds column
system("processCSV.pl regressionOHDBC3K2011.csv");
system("processCSV.pl regressionMilton2011.csv");
system("processCSV.pl regressionPickering2011.csv");
#system("processCSV.pl regressionPickeringHS2011.csv");
system("processCSV.pl regressionCenterIsland2011.csv");
system("processCSV.pl regressionNationals2011.csv");
system("processCSV.pl regressionOttawa2011.csv");
system("processCSV.pl regressionWelland2011.csv");
#system("processCSV.pl regressionLachine2011.csv");
system("processCSV.pl regressionMontreal2011.csv");
system("processCSV.pl regressionWoodstock2011.csv");
system("processCSV.pl regressionTorontoCup2011.csv");
system("processCSV.pl regressionGWNSport2011.csv");
system("processCSV.pl regressionGWNC2011.csv");
system("processCSV.pl regressionQC2011.csv");




system("db2 connect to dboat");
system("db2 drop table race");
system("db2 create table race (TEAM VARCHAR(100), CATEGORY VARCHAR(100), TIME DECIMAL(31,20), RACENO INTEGER, DISTANCE INTEGER, REGATTA VARCHAR(25), YEAR INTEGER, LANE INTEGER, RACENAME VARCHAR(50) WITH DEFAULT) ");

system("db2 load from regressionOHDBC3K2011.csv.out of del insert into race");
system("db2 load from regressionMilton2011.csv.out of del insert into race");
system("db2 load from regressionPickering2011.csv.out of del insert into race");
system("db2 load from regressionCenterIsland2011.csv.out of del insert into race");
system("db2 load from regressionNationals2011.csv.out of del insert into race");
system("db2 load from regressionOttawa2011.csv.out of del insert into race");
system("db2 load from regressionWelland2011.csv.out of del insert into race");
#system("db2 load from regressionPickeringHS2011.csv.out of del insert into race");
#system("db2 load from regressionLachine2011.csv.out of del insert into race");
system("db2 load from regressionMontreal2011.csv.out of del insert into race");
system("db2 load from regressionWoodstock2011.csv.out of del insert into race");
system("db2 load from regressionTorontoCup2011.csv.out of del insert into race");
system("db2 load from regressionGWNSport2011.csv.out of del insert into race");
system("db2 load from regressionGWNC2011.csv.out of del insert into race");
system("db2 load from regressionQC2011.csv.out of del insert into race");


#May 28  OHDBC3K
#May 28 Milton
#June 4 Pickering
#June 5 Pickering HS
#June 11 Lachine
#June 18,19 Ottawa
#June 25,26  TIDBRF
system("db2 drop table regattas");
system("db2 create table regattas (NAME VARCHAR(25), REGRESS_ORDER INTEGER, PRINT_ORDER INTEGER, WHEN DATE, WEBSITE VARCHAR(100), COMPETITIVE DECIMAL(4,2))");
# ---------------------- 500m regression order ----------------------
# -1 for regress_order means it will not be included in the regression
#system("db2 insert into regattas values " .
#            " ('OHDBC3K',     -1, -1,  DATE('2010-05-29'), 'http://www.ohdbc.ca/', 0), " . 
#            " ('Welland',     -1, -1,  DATE('2010-06-12'), 'http://www.dragon-boats.com/welland/', 0), " .
#            " ('Hamilton',    -1, -1,  DATE('2010-07-10'), 'http://www.dragon-boats.com/Hamilton/', 0), " .
#            " ('CenterIsland', 0,  3,  DATE('2010-06-19'), 'http://www.dragonboats.com/', 0), " .
#            " ('Milton',       1,  0,  DATE('2010-05-29'), 'http://www.dragon-boats.com/milton/', 0),  " .
#            " ('Pickering',    2,  1,  DATE('2010-06-07'), 'http://www.pdbc.ca/', 0),  " .
#            " ('PickeringHS',  3,  2,  DATE('2010-06-08'), 'http://www.pdbc.ca/', 0),  " . 
#            " ('GWNSport',     4,  5,  DATE('2010-07-17'), 'http://www.gwndragonboat.com/', 0), " .
#            " ('Montreal',     6,  6,  DATE('2010-07-24'), 'http://www.montrealdragonboat.com/', 0),  " .
#            " ('Ottawa',       5,  4,  DATE('2010-06-19'), 'http://www.dragonboat.net/', 0) "
#      ); 


system("db2 insert into regattas values " .
            " ('OHDBC3K',     -1, -1,  DATE('2011-05-28'), 'http://www.ohdbc.ca/', 0), "  . 
            " ('CenterIsland', 0,  4,  DATE('2011-06-25'), 'http://www.dragonboats.com/', 0), " .
            " ('Milton',       1,  0,  DATE('2011-05-28'), 'http://www.dragon-boats.com/milton/', 0),  "  .
            " ('Pickering',    2,  1,  DATE('2011-06-04'), 'http://www.pdbc.ca/', 0),  "  .
            " ('Nationals',    4,  5,  DATE('2011-07-01'), 'http://www.dragonboat.ca/', 0),  " . 
            " ('Ottawa',       5,  3,  DATE('2011-06-18'), 'http://www.dragonboat.net/', 0), " .
            " ('Welland',      3,  2,  DATE('2010-06-11'), 'http://www.lively-dragon.com/', 0), " .
       #    " ('Lachine',     -1, -1,  DATE('2011-06-11'), 'http://www.22dragons.com/site/?q=en/node/49', 0)  " 
            " ('Montreal',     7,  7,  DATE('2011-07-22'), 'http://www.montrealdragonboat.com/', 0),  "  .
            " ('Woodstock',    8,  8,  DATE('2011-08-04'), 'http://www.dragonboatwoodstock.ca', 0),  " .
            " ('Toronto Cup', -1,  -1, DATE('2011-07-23'), 'http://www.nationaldragonboat.com/', 0),  " .  
            " ('GWN Sport',     6,  6, DATE('2011-07-16'), 'http://www.gwndragonboat.com/', 0),  " .
            " ('GWNC',         9,  9,  DATE('2011-09-10'), 'http://www.gwndragonboat.com/', 0),  " .
            " ('QC',          10,  10, DATE('2011-09-17'), 'http://www.22dragons.com', 0) " 
      ); 



# ---------------------- 200m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 2, 1.0), ('Montreal', 2, 3, 1.0), ('Ottawa', 3, 1, 1.0), ('Lachine', 4, 4, 1.0), ('Belleville', 5, 5, 1.0)"); 


# ---------------------- 2000m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 1, 1.0), ('Woodstock', 2, 2, 1.0), ('Milton', 3, 3, 1.0), ('Hamilton', 4, 4, 1.0),  ('Belleville', 5, 5, 1.0)");


# export files to load to database on website.  Note that sometimes the results just list times without
# showing what heat the teams were in.  In these cases we make up imaginary raceNo -666
# but we don't want these in the website archive.  They are only useful for the regression
system("db2 export to race2011.del of del select * from race where RACENO != -666");
system("db2 export to dates2011.del of del select distinct(race.regatta), regattas.regress_order, regattas.print_order, regattas.when, regattas.website, regattas.competitive from race, regattas where race.regatta=regattas.name");



system("db2 connect");
system("db2 update race set team='MOFOS' where team='PHO KING WARRIORS'");
#system("db2 update race set TIME=128.07 where team='CSDC' and REGATTA='Pickering' and RACENO=14");
#system("db2 update race set team='NETWORK 54' where team='HYDROPHALLIC DRAGONS'");
system("db2 delete from race where team like 'OHDBC DRIFTERS'");
system("db2 delete from race where team like 'NDC REP'");
#system("db2 delete from race where team like 'SRP' and regatta like 'NDC' and raceNo=33");


print("Check for same team names with more than one gender classification: \n");
system("db2 select distinct team from race where category='Mixed' INTERSECT SELECT distinct team FROM race WHERE not category='Mixed'");

print("Check for questionable 500m times: \n");
system("db2 \"select * from race where distance=500 and (time<90 or time>240)\"");

print("Check for unknown categories: \n");
system("db2 \"select * from race where category not in ('WOMEN', 'Women', 'OPEN', 'Open', 'MIXED', 'Mixed')\" ");



system("db2 connect");
system("db2 connect reset");

